What is the difference between UNION and UNION ALL?
1399
25-May-2017
Updated on 18-Sep-2020
Akash Tripathi
28-May-2017UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
--UNION uses a distinct sort.
--UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
--UNION cannot work with a column that has a TEXT data type.
--UNION ALL can work with all data type columns.
--When we use a UNION for the TEXT type columns, SQL Server throws a runtime error. This error is not generated when we use the “UNION ALL” operator.
Manish Kumar
25-May-2017• UNION: It is use to select related information from two tables UNION command is used. Union is similar to JOIN command.
• UNION All: UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.